package com.forum.core.dao.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
import org.apache.log4j.Logger;

import com.forum.core.util.ResourceServiceConfig;

/**
 * @Company <Jinher Technology Development Company LTD.>
 * @Project <Jinher Collaboration Suite>
 * @version <1.0>
 * @Author  <yangzq>
 * @Date    <Oct 31, 2009>
 * @description < JDBC连接工具类 >
 */
public class JDBCUtil {
	private static final Logger logger = Logger.getLogger(JDBCUtil.class);
	public static Connection connectDatabase(String poolName) throws Exception {
		Connection con = null;
		Class.forName(ResourceServiceConfig.getJdbcDriverClassName());
		con = DriverManager.getConnection(ResourceServiceConfig.getJdbcURL(),
				ResourceServiceConfig.getJdbcUserName(), ResourceServiceConfig
						.getJdbcPassword());
		return con;

	}

	public static Statement getStatement(Connection conn) throws Exception {
		Statement st = null;
		try {
			st = conn.createStatement();
		} catch (Exception E) {
		}
		return st;
	}

	public static void disconnectDatabase(Connection conn) throws Exception {
		try {
			if (conn != null)
				conn.close();
		} catch (Exception E) {
		}
	}

	public static void closeStatement(Statement stmt) throws Exception {
		try {
			if (stmt != null)
				stmt.close();
		} catch (Exception E) {
		}
	}

	public static void closeResultSet(ResultSet rs) throws Exception {
		try {
			if (rs != null)
				rs.close();
		} catch (Exception E) {
		}
	}

	public static ResultSet execQuery(Statement stmt, String sql)
			throws Exception {
		try {
			if (sql == null || sql.trim().equalsIgnoreCase(""))
				return null;
			else
				return stmt.executeQuery(sql);
		} catch (SQLException E) {
			E.printStackTrace();
			System.out.print("SQLException:" + E.toString() + "SQL语句：" + sql);
			return null;
		} catch (Exception EE) {
			EE.printStackTrace();
			System.out.print("Exception:" + EE.toString() + "SQL语句：" + sql);
			return null;
		}
	}

	public static int execUpdate(Statement stmt, String sql) throws Exception {
		try {
			if (sql == null || sql.trim().equalsIgnoreCase(""))
				return -3;
			else
				return stmt.executeUpdate(sql);
		} catch (SQLException E) {
			stmt.clearBatch();
			E.printStackTrace();
			System.out.print("SQLException:" + E.toString() + "SQL语句：" + sql);
			return -3;
		} catch (Exception EE) {
			stmt.clearBatch();
			EE.printStackTrace();
			System.out.print("Exception:" + EE.toString() + "SQL语句：" + sql);
			return -3;
		}

	}

	public static boolean execSQL(Statement stmt, String sql) throws Exception {
		if (sql == null || sql.trim().equalsIgnoreCase(""))
			return false;
		else
			return stmt.execute(sql);
	}

	public static int[] execBatchUpdate(Statement stmt, String sql)
			throws Exception {
		try {
			if (sql == null || sql.trim().equalsIgnoreCase(""))
				return null;
			StringTokenizer st = new StringTokenizer(sql, ";");
			while (st.hasMoreTokens()) {
				stmt.addBatch(st.nextToken());
			}
			stmt.getConnection().setAutoCommit(false);
			int Return[] = stmt.executeBatch();
			stmt.getConnection().setAutoCommit(true);
			return Return;
		} catch (SQLException E) {
			stmt.clearBatch();
			E.printStackTrace();
			System.out.print("SQLException:" + E.toString() + "SQL语句为：" + sql);
			return null;
		} catch (Exception EE) {
			stmt.clearBatch();
			EE.printStackTrace();
			System.out.print("Exception:" + EE.toString() + "SQL语句为：" + sql);
			return null;
		}
	}

	public static int getSequence(Statement stmt, String name) throws Exception {
		ResultSet rs = execQuery(stmt, "select " + name + ".nextval from dual");
		try {
			if (rs.next())
				return rs.getInt(1);
			else
				return -9999;
		} catch (Exception e) {
			logger.error(e);
			return -9999;
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception e) {
				logger.error(e);
			}
		}
	}

	public static ResultSet execSPQuery(String poolname, String name)
			throws Exception {
		Connection conn = connectDatabase(poolname);
		CallableStatement cs = conn.prepareCall("{call " + name + "}");
		try {
			return cs.executeQuery();
		} catch (Exception e) {
			logger.error(e);
			return null;
		} finally {
			try {
				if (cs != null)
					cs.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				logger.error(e);
			}
		}
	}

	public static int execSPUpdate(String poolname, String name)
			throws Exception {
		Connection conn = connectDatabase(poolname);
		CallableStatement cs = conn.prepareCall("{call " + name + "}");
		try {
			return cs.executeUpdate();
		} catch (Exception e) {
			logger.error(e);
			return -1;
		} finally {
			try {
				if (cs != null)
					cs.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				logger.error(e);
			}
		}
	}

	public static boolean execSP(String poolname, String name) throws Exception {
		Connection conn = connectDatabase(poolname);
		CallableStatement cs = conn.prepareCall("{call " + name + "}");
		try {
			return cs.execute();
		} catch (Exception e) {
			logger.error(e);
			return false;
		} finally {
			try {
				if (cs != null)
					cs.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				logger.error(e);
			}
		}
	}

	@SuppressWarnings("unchecked")
	public static ArrayList execSP(String poolname, String name,
			ArrayList inValue, int[] outType) throws Exception {

		Connection conn = connectDatabase(poolname);
		String questionNumber = "";
		for (int i = 1; i < inValue.size() + outType.length; i++) {
			questionNumber = questionNumber + "?,";
		}
		CallableStatement cs = conn.prepareCall("{call " + name + "("
				+ questionNumber + "?)}");
		ArrayList al = new ArrayList();
		for (int i = 0; i < inValue.size(); i++) {
			cs.setObject(i + 1, inValue.get(i));
		}
		for (int i = inValue.size(); i < inValue.size() + outType.length; i++) {
			cs.registerOutParameter(i + 1, outType[i - inValue.size()]);
		}
		try {
			cs.execute();
			for (int i = inValue.size(); i < inValue.size() + outType.length; i++) {
				al.add(cs.getObject(i + 1));
			}
			return al;
		} catch (Exception e) {
			logger.error(e);
			return null;
		} finally {
			try {
				if (cs != null)
					cs.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				logger.error(e);
			}
		}
	}

	/**
	 * 用于数据库类型和java类型之间的转换
	 * @param jdbcType
	 * @return
	 */
	public static String convert(String jdbcType) {
		Map<String, String> map = new HashMap<String, String>();
		map.put("CHAR", "String");
		map.put("VARCHAR", "String");
		map.put("VARCHAR2", "String");
		map.put("LONGVARCHAR", "String");
		map.put("NUMERIC", "java.math.BigDecimal");
		map.put("NUMBER", "double");
		map.put("DECIMAL", "java.math.BigDecimal");
		map.put("BIT", "boolean");
		map.put("TINYINT", "byte");
		map.put("SMALLINT", "short");
		map.put("INTEGER", "int");
		map.put("BIGINT", "long");
		map.put("REAL", "float");
		map.put("FLOAT", "double");
		map.put("DOUBLE", "double");
		map.put("BINARY", "byte[]");
		map.put("VARBINARY", "byte[]");
		map.put("LONGVARBINARY", "byte[]");
		map.put("DATE", "java.sql.Date");
		map.put("TIME", "java.sql.Time");
		map.put("TIMESTAMP", "java.sql.Timestamp");
		map.put("BLOB", "java.sql.Blob");
		map.put("CLOB", "java.sql.Clob");
		map.put("Array", "java.sql.Array");
		map.put("REF", "java.sql.Ref");
		map.put("Struct", "java.sql.Struct");
		return (String) map.get(jdbcType);
	}

	/**
	 * 数据集列名转换为属性名
	 */
	public static String convertPropertyName(String columnName) {
		//先都转化为小写
		String lowName = columnName.toLowerCase();
		String pices[] = lowName.split("_");
		if (pices.length == 1) {
			return lowName;
		}
		StringBuffer result = new StringBuffer(pices[0]);
		for (int i = 1; i < pices.length; i++) {
			//把_开始的第2个首字母大写
			result.append(Character.toUpperCase(pices[i].charAt(0)));
			//截取除了首写字母剩下的字符
			result.append(pices[i].substring(1));
		}
		return result.toString();
	}
	
	/**
	 * @param str
	 * @return
	 * @date Mar 15, 2010 5:04:11 PM
	 * @author liuyw
	 * @description 将pojo对象中字段名称转换成为对应的数据库表列名称
	 *  转换的规则是将大写字母转换成为_+小写字母的组合；
	 */
	public static String convertPropertyToColumn(String str){
		String strs="";
		if(str!=null&&str.length()>0){
			try {
				char[] chr = str.toCharArray();
				for (int i = 0; i < chr.length; i++) {
					if (64<chr[i]&&chr[i]<91) {
						chr[i] += 32;
						strs+="_"+chr[i];
					}else{
						strs+=chr[i];
					}
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return strs;
	}

	/**
	 * 第一个字母转换成大写
	 * @param fieldName
	 * @return
	 */
	public static String convertMethodName(String fieldName) {
		StringBuffer result = new StringBuffer(fieldName);
		char firstChar = Character.toUpperCase(fieldName.charAt(0));
		result.deleteCharAt(0).insert(0, firstChar);
		return result.toString();
	}

	/**
	 * 得到全部的表名
	 * @return
	 * @throws SQLException 
	 */
	public static List<String> getAllTables() {
		Connection conn = null;
		try {
			conn = connectDatabase("");
			DatabaseMetaData metaData = conn.getMetaData();
			String user = metaData.getUserName();
			//System.out.println(user);
			String[] types = new String[1];
			types[0] = "TABLE";
			ResultSet rs2 = metaData.getTables(null, user, "%", types);
			List<String> list = new ArrayList<String>();
			while (rs2.next()) {
				String str = convertMethodName(rs2.getString("table_name")
						.toLowerCase());

				if (str.indexOf("==") == -1) {
					list.add(str);

				}
			}
			conn.close();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	/**
	 * 根据条件获取数据库表,Tbl
	 * @param tblSuffix
	 * @return
	 */
	public static List<String> getTablesByCondition(String tblSuffix) {
		List<String> list = getAllTables();
		List<String> tblList = new ArrayList<String>();
		for (int i = 0; i < list.size(); i++) {
			String table = (String) list.get(i);
			if (table != null && table.startsWith(tblSuffix)) {
				tblList.add(table);
			}
		}
		return tblList;
	}

	/**
	 * 根据表名获取字段属性
	 * @param tableName
	 * @return
	 */

	public static List<String> getColumsByTable(String tableName) {
		if ("".equals(tableName) || tableName == null) {
			return null;
		}
		List<String> list = new ArrayList<String>();
		String sql = "select * from " + tableName;
		Connection conn=null;
		try {
			conn = connectDatabase("");
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData orsm = rs.getMetaData();
			for (int i = 1; i <= orsm.getColumnCount(); i++) {
				String columnName = orsm.getColumnName(i);
				list.add(columnName);

			}
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	/**
	 * 根据表名获取指定字段的数据类型
	 * @param table
	 * @param colum
	 * @return
	 */
	public static String getTypeByTabAndColum(String tableName, String columName) {
		if ("".equals(tableName) || tableName == null) {
			return null;
		}
		String typeName = "";
		Connection conn = null;
		Map<String, String> map = new HashMap<String, String>();
		String sql = "select * from " + tableName;
		try {
			conn = connectDatabase("");
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData orsm = rs.getMetaData();
			//把字段 key,类型value放到map中
			for (int i = 1; i <= orsm.getColumnCount(); i++) {
				String columnType = orsm.getColumnTypeName(i);
				String columnName = orsm.getColumnName(i);
				
				map.put(columnName, columnType);
			}
			//根据key到value
			typeName = map.get(columName);
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return typeName;
	}
	
   
}
